﻿using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model.VR;
using HCQ2_Model.VRDD.InputDto._210908;
using HCQ2_Model.VRDD.OutputDto._210908;
using HCQ2_Model.VRDD.OutputDto._211217;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRDDStoreTypeUserDAL: IT_VRDDStoreTypeUserDAL
    {
        /// <summary>
        ///  获取店铺数据字典类别列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<OutputStoreDictType> GetDictTypeByStore(InputStoreDictType param)
        {
            sb?.Clear();
            sb.Append($@"SELECT * FROM (
            SELECT i.code_name AS text,i.code_value AS value,ISNULL(d.num,0)AS num,number=(SELECT COUNT(0) FROM 
            dbo.T_VRDDStoreTypeUser WHERE store_id={param.storeID} AND store_code='{param.dictType}' AND store_type=d.store_type) FROM 
            (SELECT dictCode as store_type,COUNT(0)AS num FROM T_VRDDStoreDictType WHERE store_id={param.storeID} GROUP BY dictCode)d RIGHT JOIN
            (SELECT code_name,code_value FROM dbo.T_ItemCodeMenum WHERE item_id IN
            (SELECT item_id FROM dbo.T_ItemCode WHERE item_code='{param.dictType}')) i ON d.store_type=i.code_value)s ");
            if (param.status > -1)
            {
                if (param.status > 0)
                    sb.Append($" WHERE s.num>0 ");
                else
                    sb.Append($" WHERE s.num<1 ");
            }
            return SqlHelper.ExecuteList<OutputStoreDictType>(sb.ToString());
        }
        /// <summary>
        ///  获取人员详情
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<OutputStoreUserInfo> GetStoreUserInfo(InputStoreUserInfo param)
        {
            sb?.Clear();
            sb.Append($@"SELECT u.*,status=(CASE WHEN ISNULL(s.dd_id,0)>0 THEN 1 ELSE 0 END) FROM 
            (SELECT user_id,user_guid AS userID,user_name AS userName,login_name AS loginName,user_phone AS userPhone,
            user_img AS userImg FROM dbo.T_User WHERE user_phone='{param.userPhone}')u LEFT JOIN 
            (SELECT dd_id FROM T_VRDDStoreTypeUser WHERE store_type='{param.dictCode}' AND store_code='{param.dictType}' ");
            if (param.storeID > 0)
                sb.Append($" AND store_id={param.storeID} ");
            sb.Append(")s ON u.user_id=s.dd_id;");
            return SqlHelper.ExecuteList<OutputStoreUserInfo>(sb.ToString());
        }
        /// <summary>
        ///  获取店铺类别人员列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<OutputGetStoreTypeUser> GetStoreTypeUser(InputGetStoreTypeUser param)
        {
            sb?.Clear();
            sb.Append($@"SELECT u.*,s.createTime FROM  (SELECT dd_id,CONVERT(varchar(100),create_date,120)AS createTime
            FROM T_VRDDStoreTypeUser WHERE store_id={param.storeID} AND store_type='{param.dictCode}' AND store_code='{param.dictType}')s INNER JOIN
            (SELECT user_id,user_guid AS userID,user_name AS userName,login_name AS loginName,user_phone AS userPhone,
            user_img AS userImg FROM dbo.T_User)u ON s.dd_id=u.user_id;");
            return SqlHelper.ExecuteList<OutputGetStoreTypeUser>(sb.ToString());
        }
        /// <summary>
        ///  获取店铺商品列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<OutputStoreGoodList> GetStoreGoodList(InputStoreGoodList param)
        {
            sb?.Clear();
            string tempStr = " AND rel_id IN(SELECT file_id FROM dbo.T_DocumentInfo WHERE if_remove=0 AND file_status=5)";
            sb.Append($@"SELECT TOP {param.rows} * FROM (
            SELECT ROW_NUMBER() OVER(ORDER BY  ");
            switch (param.priceOrder)
            {
                case 4: sb.Append(" create_time DESC "); break;//创建时间降序 
                case 5: sb.Append(" file_order DESC "); break;//序号降序 
                case 6: sb.Append(" file_order ASC "); break;//序号升序
                default:sb.Append(" update_time DESC "); break;//修改时间降序
            }
            sb.Append($@")AS num,file_id AS fileId,isSoldOut,file_name AS fileName,file_note AS fileNote,
            fileImage=(SELECT TOP 1 file_url FROM T_DocBagFileRecord WHERE file_id=i.file_id AND file_type=0),
            filePrice=(SELECT MIN(ori_price) FROM dbo.T_VRGoodDisiPrice WHERE file_id=i.file_id),
            rate0=ISNULL((SELECT TOP 1 rate FROM dbo.T_VRGoodDiscount WHERE file_id=i.file_id AND user_level=0),0),          
            adimageNum=(SELECT COUNT(0) FROM T_VRDDCircleRelGood rg WHERE dd_id=i.file_id AND rel_type='ad-image' {tempStr}),
            imageNum=(SELECT COUNT(0) FROM T_VRDDCircleRelGood rg WHERE dd_id=i.file_id AND rel_type='image' {tempStr}),
            pimageNum=(SELECT COUNT(0) FROM T_VRDDCircleRelGood rg WHERE dd_id=i.file_id AND rel_type='p-image' {tempStr}),
            audioNum=(SELECT COUNT(0) FROM T_VRDDCircleRelGood rg WHERE dd_id=i.file_id AND rel_type='audio' {tempStr}),
            vrNum=(SELECT COUNT(0) FROM T_VRDDCircleRelGood rg WHERE dd_id=i.file_id AND rel_type='works' {tempStr}),
            specNum=(SELECT COUNT(0) FROM dbo.T_VRDDGoodSpecification WHERE file_id=i.file_id),
            specOption=(SELECT (SELECT STUFF(
	            (SELECT ','+specName FROM dbo.T_VRDDGoodSpecification
	            WHERE file_id=a.file_id FOR XML PATH('')),1,1,''
            )) FROM dbo.T_VRDDGoodSpecification a WHERE file_id=i.file_id GROUP BY a.file_id),
            rateNum=(SELECT COUNT(0) FROM dbo.T_VRGoodDiscount WHERE file_id=i.file_id),
            gps_x AS gpsX,gps_y AS gpsY,doc_number AS floor,is_mian AS isMian,is_batch AS isBatch,isEntity,
            store_top AS isStroeTop FROM dbo.T_DocumentInfo i WHERE file_id IN(
	            SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE '{param.folderPath}%'
            ) AND page_type='DDCircle' AND file_status={param.fileStatus} ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (file_name LIKE '%{param.keyword}%' OR file_note LIKE '%{param.keyword}%') ");
            sb.Append($")info WHERE info.num>{(param.page - 1) * param.rows};");
            return SqlHelper.ExecuteList<OutputStoreGoodList>(sb.ToString());
        }

        /// <summary>
        ///  获取商品类别目录
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<OutputFolderType> GetGoodsFolderType(InputFolderType param)
        {
            sb?.Clear();
            sb.Append($@"SELECT folder_name AS FolderName,folder_note AS FolderNote,folder_id AS FolderID,
            folder_path AS FolderPath,folder_order AS FolderOrder,folder_url AS FolderImage,
            FileNumber=(SELECT COUNT(0) FROM dbo.T_DocumentFolderRelation WHERE folder_id=f.folder_id)
             FROM dbo.T_DocumentFolder f WHERE f.folder_pid={param.FolderID} AND f.if_remove={param.IsRemove} ORDER BY FolderOrder DESC;");
            return SqlHelper.ExecuteList<OutputFolderType>(sb.ToString());
        }
        /// <summary>
        ///  获取被删除的目录下是否存在已上架的商品
        /// </summary>
        /// <param name="folder_id"></param>
        /// <returns></returns>
        public int GetGoodsFolderTypeNum(int folder_id)
        {
            sb?.Clear();
            sb.Append($@" SELECT COUNT(0)AS num FROM dbo.T_DocumentInfo WHERE file_id IN(
             SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_id={folder_id})
              AND if_remove=0 AND file_status=5;");
            return SqlHelper.ExecuteNonQuery(sb.ToString());
        }
        /// <summary>
        ///  获取我服务的店铺
        /// </summary>
        /// <param name="param"></param>
        /// <param name="user_id">用户id</param>
        /// <returns></returns>
        public List<OutputByMyServe> GetStoreByMyServe(InputByMyServe param, int user_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT folder_name AS FolderName,folder_note AS FolderNote,folder_id AS FolderID,
            folder_path AS FolderPath,folder_order AS FolderOrder,folder_url AS FolderImage,
            OrderNumber=(SELECT COUNT(0) FROM dbo.N_Order 
            WHERE OrderCode IN(SELECT OrderCode FROM dbo.N_OrderCommodityInfo WHERE file_id IN(SELECT file_id FROM 
            dbo.T_DocumentFolderRelation WHERE folder_path LIKE f.folder_path+'%')) AND StorageStatus in({string.Join(",",param.storageStatus)}) AND OrderState IN(2,4)),
            OrderTime=(SELECT  TOP 1 CONVERT(varchar(100),OrderCreateTime,120) AS OrderCreateTime FROM dbo.N_Order 
            WHERE OrderCode IN(SELECT OrderCode FROM dbo.N_OrderCommodityInfo WHERE file_id IN(SELECT file_id FROM 
            dbo.T_DocumentFolderRelation WHERE folder_id=f.folder_id)) AND OrderState IN(2,4) ORDER BY OrderCreateTime DESC),
            ItemNote=(SELECT TOP 1 code_note FROM dbo.T_ItemCodeMenum WHERE item_id IN(SELECT item_id FROM dbo.T_ItemCode
            WHERE item_code='{param.dictType}') AND code_value='{param.dictCode}') FROM dbo.T_DocumentFolder f WHERE folder_id IN(
            SELECT store_id FROM T_VRDDStoreTypeUser WHERE dd_id={user_id} AND store_code='{param.dictType}' AND 
            store_type='{param.dictCode}')  ORDER BY folder_order DESC;");
            return SqlHelper.ExecuteList<OutputByMyServe>(sb.ToString());
        }
        
    }
}